<?php
class serviceQuery extends EngineSupervisor{
    public $db;

    public function __construct(){
            $this->db = new dataBase();
    }

    /**
     * Add new service to database
     * @param array $post
     * @author Grzegorz Wiater <g.wiater@gmail.com>
     */
    public function add($post){
        $companyName = "";
        $nip = "";
        $newUserInfo = false;
        if(isset($post['includeCompanyData'])){
            $companyName = trim($post['companyName']);
            $nip = trim($post['nip']);
        }
        
        if(isset($_SESSION[PREFIX]['user']['id']))
        {
            $userId = $_SESSION[PREFIX]['user']['id'];
        }
        else
        {
            $common = new CommonQuery();
            $query = "SELECT * FROM users WHERE email=?";
            $user = $this -> db -> PdoQuery($query, array($post['email']));
            if(isset($user[0]['id']) && $user[0]['id'] !="")
            {
                $userId = $user[0]['id'];    
            }
            else
            {
                $control = md5(rand(100, 130000).time());
                $userValues = array(
                    'name' => $post['name'],
                    'surname' => $post['surname'], 
                    'email' => $post['email'],
                    'password' => md5($post['email']),
                    'control' => $control
                );
                
                $us = $this->db->insert('users', $userValues , 'users_id_seq');
                $query = "SELECT id FROM users WHERE id=(SELECT max(id) FROM users)";
                $newuser = $this -> db -> PdoQuery($query, array());
                $userId = $newuser[0]['id'];
                $newUserInfo = true;
                
                $mailingContent = file_get_contents(MAIN_PATH.'public/mailing/registrationMail.html');
                $mailingContent = str_replace('%%url%%', SITE_ADDRESS, $mailingContent);
                $mailingContent = str_replace('%%secret%%', $control, $mailingContent);
               
                $this -> SendEmail($_POST['email'], $mailingContent, "Aktywacja w serwisie SOSpraca.", "SOSpraca.pl");
                
            }
        }
        
        $serviceValues = array(
            'user_id' => $userId,
            'category_id' => $post['category'],
            'price_per_hour' => !empty($post['pricePerHour'])? str_replace(',', '.', $post['pricePerHour']): '0.0',
            'price_per_service' => !empty($post['pricePerService'])? str_replace(',', '.', $post['pricePerService']) : '0.0',
            'description' => trim($post['description']),
            'company_name' => $companyName,
            'nip' => $nip,
            'lon' => $post['lon'],
            'lat' => $post['lat'],
            'distance' => $post['distance'],
            'creation_date' => date('Y-m-d H:i:s'),
            'service_name' => trim($post['serviceName'])
        );
//        pre($serviceValues);
        $serviceLocations = array();
        if(!empty($post['points'])){
            foreach($post['points'] as $key =>$point){
                $location = array(
                    'lat' => $point['lat'],
                    'lng' => $point['lng'],
                    'point_order' => $key
                );
                $serviceLocations[] = $location;
            }
        }else{
            throw new Exception('Location of service has to be set!');
        }

        try{
//            $this->db->pdo->beginTransaction();
            $this->db->insert('service', $serviceValues , 'service_id_seq');
//            die();
//            $serviceId = $this->db->getLastInsertId();

//            foreach($serviceLocations as $loc){
//                $loc['service_id'] = $serviceId;
//                $this->db->insert('service_location', $loc);
//            }
//            
//            $this->db->pdo->commit();
            return $newUserInfo;
        }catch(Exception $e){
            throw $e;
        }
    }

    protected function getCategoriesByChildId($childId){
        $query = "SELECT id, name, parent FROM categories WHERE parent =
                (SELECT parent FORM FROM categories WHERE id=(
                    (SELECT parent FROM categories WHERE id = ?)))";
        
        return $this->db->ExecuteQuery($query, array($childId));
    }
    protected function getCategoriesById($Id){
        $query = "SELECT id, name, parent FROM categories WHERE parent =
                    (SELECT parent FROM categories WHERE id = ?)";

        return $this->db->ExecuteQuery($query, array($Id));
    }

    protected function getParentIdById($Id){
        $query = "SELECT parent FROM categories WHERE id = ? LIMIT 1";
        $result = $this->db->ExecuteQuery($query, array($Id));
        return $result[0]['parent'];
    }
    protected function createCategoriesArr($cat){
        $categories = array();
        if(!empty($cat)){
            foreach($cat as $category){
                $categories[$category['id']] = $category['name'];
            }
        }
        return $categories;
    }
    
    public function loadCategories(){
        $request = array_merge($_POST, $_GET);
        $comonQuery = new CommonQuery();
        $response = new stdClass();

        $firstSelected = null;
        $secSelected = null;
        $thirdSelected = null;

        $catComboFirst = array();
        $catComboSec = array();
        $catComboThird = array();
        switch($request['combo']){
            case 'first':{
//                $cat = $comonQuery->select('categories', array('id', 'name', 'parent'), array('parent' => $request['parent_id']));
//                $catComboSec = $this->createCategoriesArr($cat);

                $catOne = $this->getCategoriesByChildId($request['parent_id']);
                if(!empty($catOne)){
                    $catComboFirst = $this->createCategoriesArr($catOne);
                    $firstSelected = $this->getParentIdById($request['parent_id']);

                    $catSec = $this->getCategoriesById($request['parent_id']);
                    $catComboSec = $this->createCategoriesArr($catSec);
                    $secSelected = $request['parent_id'];

                    $catThird = $comonQuery->select('categories', array('id', 'name', 'parent'), array('parent' => $secSelected));
                    $catComboThird = $this->createCategoriesArr($catThird);

                }else{
                    $cat = $comonQuery->select('categories', array('id', 'name', 'parent'), array('parent' => $request['parent_id']));
                    $catComboSec = $this->createCategoriesArr($cat);
                }
            }break;
            case 'sec':{
                $cat = $comonQuery->select('categories', array('id', 'name', 'parent'), array('parent' => $request['parent_id']));
                $catComboThird = $this->createCategoriesArr($cat);
            }break;
            case 'third':{
                $cat = $comonQuery->select('categories', array('id', 'name', 'parent'), array('parent' => $request['parent_id']));
                if(!empty($cat)){
                    $catComboThird = $this->createCategoriesArr($cat);
                    
                    $catSec = $this->getCategoriesByChildId($cat[0]['id']);
                    $catComboSec = $this->createCategoriesArr($catSec);
                    $secSelected  = $request['parent_id'];

                    $catOne = $this->getCategoriesByChildId($catSec[0]['id']);
                    $catComboFirst = $this->createCategoriesArr($catOne);
                    $firstSelected = $catSec[0]['parent'];
                }
            }break;
            default:{
            }
            break;
        }


//        if(isset($request['parent_id'])){
//            $cat = $comonQuery->select('categories', array('id', 'name'), array('parent' => $request['parent_id']));
//        }else{//child_id
//            $query = "SELECT id, name FROM categories WHERE parent =
//                (SELECT parent FORM FROM categories WHERE id=(
//                    (SELECT parent FROM categories WHERE id = ?)))";
//            $cat = $this->db->ExecuteQuery($query, array($request['child_id']));
//        }
//
//        $categories = array();
//        if(!empty($cat)){
//            foreach($cat as $category){
//                $categories[$category['id']] = $category['name'];
//            }
//        }

        ob_get_clean();
        //$response->categories = $categories;

        $response->firstSelected = $firstSelected;
        $response->secSelected = $secSelected;
        $response->thirdSelected = $thirdSelected;
        
        $response->catComboFirst = $catComboFirst;
        $response->catComboSec = $catComboSec;
        $response->catComboThird = $catComboThird;
        echo json_encode($response);
        exit;
    }

    public function getCategoriesByCategoryId($catId){
        $query = "SELECT id, name FROM categories WHERE parent =
                    (SELECT parent FROM categories WHERE id = ?)";
        return $this->db->ExecuteQuery($query, array($catId));
    }

    public function validate($post){
        $validator = new Validator();
        $valid = true;
        //service name
        if(strlen($post['serviceName']) == 0){
            $_SESSION[PREFIX]['message']['form']['serviceName'] = 'Proszę wypełnić imię';
            $valid = false;
        }
        //firstname
        if(strlen($post['name']) == 0){
            $_SESSION[PREFIX]['message']['form']['name'] = 'Proszę wypełnić imię';
            $valid = false;
        }
        //lastname
        if(strlen($post['surname']) == 0){
            $_SESSION[PREFIX]['message']['form']['surname'] = 'Proszę wypełnić nazwisko';
            $valid = false;
        }

        //email
        if(!$validator->ValidateEmail($post['email'])){
            $_SESSION[PREFIX]['message']['form']['email'] = 'Email ma błędny format';
            $valid = false;
        }
        //category
        $query = "SELECT id, name FROM categories WHERE id = ?";
        $category = $this->db->ExecuteQuery($query, array($post['category']));
        if(count($category) == 0){
            $_SESSION[PREFIX]['message']['form']['category'] = 'Błędna kategoria';
            $valid = false;
        }
        //price per hour
        $post['pricePerHour'] = str_replace(',', '.', $post['pricePerHour']);
        if(!is_numeric($post['pricePerHour'])){
            $_SESSION[PREFIX]['message']['form']['pricePerHour'] = 'Proszę wpisać liczbę';
            $valid = false;
        }
        //price per service
        $post['pricePerService'] = str_replace(',', '.', $post['pricePerService']);
        if(!is_numeric($post['pricePerHour'])){
            $_SESSION[PREFIX]['message']['form']['pricePerService'] = 'Proszę wpisać liczbę';
            $valid = false;
        }
        //company
        if(isset($post['includeCompanyData'])){
            //companyname
            if(strlen(trim($post['companyName'])) == 0){
                $_SESSION[PREFIX]['message']['form']['companyName'] = 'Proszę wpisać nazwę firmy';
                $valid = false;
            }
            //nip
            $nipValid = $validator->ValidateNip($post['nip'], false);
            if($nipValid !== true){
                $_SESSION[PREFIX]['message']['form']['nip'] = $nipValid;
                $valid = false;
            }
        }
        
        //assign values
        $_SESSION[PREFIX]['filtr']['serviceName'] = $post['serviceName'];
        $_SESSION[PREFIX]['filtr']['name'] = $post['name'];
        $_SESSION[PREFIX]['filtr']['surname'] = $post['surname'];
        $_SESSION[PREFIX]['filtr']['email'] = $post['email'];
        $_SESSION[PREFIX]['filtr']['category'] = $post['category'];
        $_SESSION[PREFIX]['filtr']['pricePerHour'] = $post['pricePerHour'];
        $_SESSION[PREFIX]['filtr']['pricePerService'] = $post['pricePerService'];
        $_SESSION[PREFIX]['filtr']['description'] = $post['description'];
        $_SESSION[PREFIX]['filtr']['companyName'] = $post['companyName'];
        $_SESSION[PREFIX]['filtr']['nip'] = $post['nip'];
        $_SESSION[PREFIX]['filtr']['includeCompanyData'] = isset($post['includeCompanyData']);
        return $valid;
    }
    
    public function getLastServices($number){
        $query = "SELECT s.id as service_id, trim(both ' ' from s.company_name) as companyname, u.name, u.surname, u.phone, s.* FROM service s, users u
            WHERE u.flag_confirm=true AND s.user_id=u.id
            ORDER BY creation_date desc LIMIT ?";

        return $this->db->ExecuteQuery($query, array($number));
    }
}